package com.swn.common.util;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
/**
 * 功能：懒加载当前数据库用户下表参数工具类（目前服务器内存较大，不建议使用懒加载）
 * 说明：DbTableLazyLoadUtil.map中，key是表名，value是Map<String, Object>。
 * 其中的key代表参数名，value是对应的单表操作的sql或集合：
 * COLNAMES（字段名列表）、PREPARED_PKWHERE（预编译主键查询条件语句 ）、PREPARED_SELECTSQL（默认查询语句）、
 * PREPARED_GETSQL（预编译获取单条数据语句）、PREPARED_REMOVESQL（预编译删除单条数据语句）、COLCODELIST（获取字段列）
 * 
 * 注意：
 *  1.此类不需要在监听中启动；
 *  2.使用时请将该类名替换为DbTableUtil.java，便于在DbHeper内调用
 * @author taozhaocai
 * @Date 2016-7-6  上午09:47:15
 */
public class DbTableLazyLoadUtil {
	private static Logger logger = Logger.getLogger("DbTableLazyLoadUtil.class");
	/** 保存表相关信息的map ***/
	public static Map<String, Map<String, Object>> map = new HashMap<String, Map<String, Object>>();
	/** 是否懒加载 ***/
	public static boolean isLazyLoad = true;
	
	
	 /**
	  * 设置单表的参数
	  * 
	  * @param tableName
	  * @param jdbcTemplate void
	  *
	  * @Author taozhaocai
	  * @Date 2016-5-6 上午10:30:38 
	  */
	 public static void setTableMap(String tableName,JdbcTemplate jdbcTemplate){
		 //String colNames = "select wmsys.wm_concat(column_name) from user_col_comments where table_name=upper('"+tableCode+"')";
		 //String keyCol = "SELECT COL.COLUMN_NAME FROM USER_CONSTRAINTS CON,  USER_CONS_COLUMNS COL WHERE CON.CONSTRAINT_NAME = COL.CONSTRAINT_NAME AND CON.CONSTRAINT_TYPE='P' AND COL.TABLE_NAME = '"+tableName+"'";
		 String keyCol = "select COLUMN_NAME from information_schema.COLUMNS where COLUMN_KEY = 'PRI' and table_name = '"+tableName+"'";
		 List<Map<String,Object>> keyColMapList = jdbcTemplate.queryForList(keyCol);
		 /** 所操纵数据库表名称 */
		 String TABLE_NAME = tableName;
		 /** 字段名列表 */
		 //String COLNAMES = jdbcTemplate.queryForObject(colNames, String.class);
		 String COLNAMES = wmConcat(jdbcTemplate,tableName);
		 /** 预编译主键查询条件语句 */
		 String PREPARED_PKWHERE = concatkeyColName(keyColMapList);
		 /** 默认查询语句 */
		 StringBuffer PREPARED_SELECTSQL = new StringBuffer();
		 PREPARED_SELECTSQL.append("SELECT ").append(COLNAMES).append(" FROM ").append(TABLE_NAME);
		 /** 预编译获取单条数据语句 */
		 StringBuffer PREPARED_GETSQL = new StringBuffer();
		 PREPARED_GETSQL.append(PREPARED_SELECTSQL).append(" WHERE ").append(PREPARED_PKWHERE);
		 /** 预编译删除单条数据语句 */
		 StringBuffer PREPARED_REMOVESQL = new StringBuffer();
		 PREPARED_REMOVESQL.append("DELETE FROM ").append(TABLE_NAME).append(" WHERE ").append(PREPARED_PKWHERE);
		 /** 获取字段列 */
		 List<String> COLCODELIST = java.util.Arrays.asList(COLNAMES.split(",")); 
		 Map<String, Object> paramMap = new HashMap<String, Object>();
		 paramMap.put("COLNAMES", COLNAMES);
		 paramMap.put("PREPARED_PKWHERE", PREPARED_PKWHERE);
		 paramMap.put("PREPARED_SELECTSQL", PREPARED_SELECTSQL.toString());
		 paramMap.put("PREPARED_GETSQL", PREPARED_GETSQL.toString());
		 paramMap.put("PREPARED_REMOVESQL", PREPARED_REMOVESQL.toString());
		 paramMap.put("COLCODELIST", COLCODELIST);
		 map.put(TABLE_NAME, paramMap);
	 }
	 
	 /**
	  * 有的oracle数据库不支持wmsys.wm_concat函数，则调用该方法
	  * @param jdbcTemplate
	  * @param tableName
	  * @return
	  */
	 public static String wmConcat(JdbcTemplate jdbcTemplate,String tableName){
		 StringBuffer colNames = new StringBuffer("");
		 StringBuffer sql = new StringBuffer();
		 sql.append("select COLUMN_NAME from information_schema.COLUMNS where table_name = '");
		 sql.append(tableName).append("')");
		 List<Map<String,Object>> list = jdbcTemplate.queryForList(sql.toString());
		 for (int i = 0; i < list.size(); i++) {
			 Map<String,Object> map = list.get(i);
			 if(i!=list.size()-1){
				 colNames.append(map.get("COLUMN_NAME")).append(",");
			 }else{
				 colNames.append(map.get("COLUMN_NAME"));
			 }
			
		}
		 return colNames.toString();
	 }
	 
	 
	 /**
	  * 表的对象map懒加载公共方法
	  * 主要在懒加载模式下调用
	  * @param tableName void
	  *
	  * @Author taozhaocai
	  * @Date 2016年7月6日 上午9:34:51
	  */
	 private static void loadTableMap(String tableName){
		 if(isLazyLoad==false){
			 return ;
		 }
		 Object obj = map.get(tableName.toUpperCase()) ;
		 if(obj==null){
			 JdbcTemplate jdbcTemplate	= (JdbcTemplate)ServiceLocator.getBean("jdbcTemplate");
			 logger.info("load table " +tableName);
			 setTableMap(tableName,jdbcTemplate);
		 }
	 }
	 
	 /**
	  * 
	  * 获取某个表的列代码，用逗号间隔
	  * @param tableName
	  * @return String
	  *
	  * @Author taozhaocai
	  * @Date 2016-5-6 上午10:31:09 
	  */
	 public static String COLNAMES(String tableName){
		 if(tableName!=null&&!"".equals(tableName)){
			loadTableMap(tableName);
			return  (String)(map.get(tableName.toUpperCase())).get("COLNAMES");
		 }
		 return null;
	 }
	 
	
	 
	 /**
	  * 获取"预编译主键查询条件语句"
	  * 
	  * @param tableName
	  * @return String
	  *
	  * @Author taozhaocai
	  * @Date 2016-5-6 上午10:34:38 
	  */
	 public static String PREPARED_PKWHERE(String tableName){
		 if(tableName!=null&&!"".equals(tableName)){
			loadTableMap(tableName);
			return  (String)(map.get(tableName.toUpperCase())).get("PREPARED_PKWHERE");
		 }
		 return null;
	 }
	 
	 /**
	  * 获取默认查询语句
	  * 
	  * @param tableName
	  * @return String
	  *
	  * @Author taozhaocai
	  * @Date 2016-5-6 上午11:01:05 
	  */
	 public static String PREPARED_SELECTSQL(String tableName){
		 if(tableName!=null&&!"".equals(tableName)){
			 loadTableMap(tableName);
			return  (String)(map.get(tableName.toUpperCase())).get("PREPARED_SELECTSQL");
		 }
		 return null;
	 }
	 
	 /**
	  * 获取预编译获取单条数据语句 
	  * 
	  * @param tableName
	  * @return String
	  *
	  * @Author Administrator
	  * @Date 2016-5-6 上午11:22:10 
	  */
	 public static String PREPARED_GETSQL(String tableName){
		 if(tableName!=null&&!"".equals(tableName)){
			 loadTableMap(tableName);
			return  (String)(map.get(tableName.toUpperCase())).get("PREPARED_GETSQL");
		 }
		 return null;
	 }
	 
	 /**
	  * 获取“预编译删除单条数据语句 ”
	  * 
	  * @param tableName
	  * @return String
	  *
	  * @Author Administrator
	  * @Date 2016-5-6 上午11:22:54 
	  */
	 public static String PREPARED_REMOVESQL(String tableName){
		 if(tableName!=null&&!"".equals(tableName)){
			loadTableMap(tableName);
			return  (String)(map.get(tableName.toUpperCase())).get("PREPARED_REMOVESQL");
		 }
		 return null;
	 }
	 
	 /**
	  * 获取“获取字段列”
	  * 
	  * @param tableName
	  * @return List<String>
	  *
	  * @Author Administrator
	  * @Date 2016-5-6 上午11:23:35 
	  */
	 public static List<String> COLCODELIST(String tableName){
		 if(tableName!=null&&!"".equals(tableName)){
			 loadTableMap(tableName);
			return  (List<String>)(map.get(tableName.toUpperCase())).get("COLCODELIST");
		 }
		 return null;
	 }
	 
	 
	 
	 /**
	  * 拼装主键具名参数字符串
	  * 
	  * @param keyColMapList
	  * @return String
	  *
	  * @Author taozhaocai
	  */
	 private static String concatkeyColName(List<Map<String,Object>> keyColMapList){
		 StringBuffer concatkeyCol = new StringBuffer();
		 for (int i = 0; i < keyColMapList.size(); i++) {
			 Map<String,Object> map = keyColMapList.get(i);
			 String key = (String)map.get("column_name");
			 if(i==keyColMapList.size()-1){
				 concatkeyCol.append(key).append("=:").append(key);
			 }else{
				 concatkeyCol.append(key).append("=:").append(key).append(" and ");
			 }
		}
		 return concatkeyCol.toString();
	 }

}
